Databases

class: center, middle, inverse, title-slide

# Databases
### Kirill & Nicolas
### cynkra GmbH
### March 15, 2022

---

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- **Joins**
- The {dm} package
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/21.webp")
background-size: 40%
background-position: 100% 100%

# Joins

.pull-left[
- Usage
- Join sources
- Mounting

Script: `databases_21.R`

```r
library(tidyverse)
```
]

---

background-image: url("data:image/png;base64,#images/21-frame.webp")
background-size: 40%
background-position: 100% 100%

# Joins

.pull-left[
- Usage
- Join sources
- Mounting

Script: `databases_21.R`

```r
library(tidyverse)
```
]

---

# A second table

.pull-left[

```r
academy <- tbl(con_duckdb, "academy")
academy_sqlite <- tbl(con_sqlite, "academy")

academy %>%
  count(status)
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 2]
# Database: duckdb_connection
 status n
 &lt;chr&gt; &lt;dbl&gt;
1 Award not yet introduced 3
2 Nominated 36
3 Ineligible 23
4 Won Special Achievement 1
5 Won 17
</CODE></PRE>
]

.pull-right[

```r
academy
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;academy&gt; [?? x 3]
# Database: duckdb_connection
 film award_type status 
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; 
 1 Toy Story Animated Feature Award not yet introduced
 2 Toy Story Original Screenplay Nominated 
 3 Toy Story Adapted Screenplay Ineligible 
 4 Toy Story Original Score Nominated 
 5 Toy Story Original Song Nominated 
 6 Toy Story Other Won Special Achievement 
 7 A Bug&#039;s Life Animated Feature Award not yet introduced
 8 A Bug&#039;s Life Adapted Screenplay Ineligible 
 9 A Bug&#039;s Life Original Score Nominated 
10 Toy Story 2 Animated Feature Award not yet introduced
# … with more rows
</CODE></PRE>
]

---

# Left join

The most frequent kind of join.

.pull-left[
## Unsafe

```r
academy %>%
  left_join(pixar_films)
```

<PRE class="fansi fansi-message"><CODE>Joining, by = &quot;film&quot;
</CODE></PRE><PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 7]
# Database: duckdb_connection
 film award_type status number release_d… run_t… film_…
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 Toy Story Animated Fea… Award… 1 1995-11-22 81 G 
 2 Toy Story Original Scr… Nomin… 1 1995-11-22 81 G 
 3 Toy Story Adapted Scre… Ineli… 1 1995-11-22 81 G 
 4 Toy Story Original Sco… Nomin… 1 1995-11-22 81 G 
 5 Toy Story Original Song Nomin… 1 1995-11-22 81 G 
 6 Toy Story Other Won S… 1 1995-11-22 81 G 
 7 A Bug&#039;s Life Animated Fea… Award… 2 1998-11-25 95 G 
 8 A Bug&#039;s Life Adapted Scre… Ineli… 2 1998-11-25 95 G 
 9 A Bug&#039;s Life Original Sco… Nomin… 2 1998-11-25 95 G 
10 Toy Story 2 Animated Fea… Award… 3 1999-11-24 92 G 
# … with more rows
</CODE></PRE>
]

.pull-right[
## Better

```r
academy %>%
  left_join(pixar_films, by = "film")
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 7]
# Database: duckdb_connection
 film award_type status number release_d… run_t… film_…
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 Toy Story Animated Fea… Award… 1 1995-11-22 81 G 
 2 Toy Story Original Scr… Nomin… 1 1995-11-22 81 G 
 3 Toy Story Adapted Scre… Ineli… 1 1995-11-22 81 G 
 4 Toy Story Original Sco… Nomin… 1 1995-11-22 81 G 
 5 Toy Story Original Song Nomin… 1 1995-11-22 81 G 
 6 Toy Story Other Won S… 1 1995-11-22 81 G 
 7 A Bug&#039;s Life Animated Fea… Award… 2 1998-11-25 95 G 
 8 A Bug&#039;s Life Adapted Scre… Ineli… 2 1998-11-25 95 G 
 9 A Bug&#039;s Life Original Sco… Nomin… 2 1998-11-25 95 G 
10 Toy Story 2 Animated Fea… Award… 3 1999-11-24 92 G 
# … with more rows
</CODE></PRE>
]

---

# Left join

Computed on the database, original data unchanged.

```r
academy %>%
  left_join(pixar_films, by = "film") %>%
  show_query()
```

```
<SQL>
SELECT "LHS"."film" AS "film", "award_type", "status", "number", "release_date", "run_time", "film_rating"
FROM "academy" AS "LHS"
LEFT JOIN "pixar_films" AS "RHS"
ON ("LHS"."film" = "RHS"."film")
```

---

# Join with preparation

The right-hand side in the join should come from a variable.

.pull-left[
## Prepare RHS

```r
academy_won <-
 academy %>%
 filter(status == "Won") %>%
 count(film, name = "n_won")
academy_won
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 2]
# Database: duckdb_connection
 film n_won
 &lt;chr&gt; &lt;dbl&gt;
 1 Monsters, Inc. 1
 2 Finding Nemo 1
 3 The Incredibles 2
 4 Ratatouille 1
 5 WALL-E 1
 6 Up 2
 7 Toy Story 3 2
 8 Brave 1
 9 Inside Out 1
10 Coco 2
# … with more rows
</CODE></PRE>
]

.pull-right[

```r
pixar_films %>%
  left_join(academy_won, by = "film")
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 6]
# Database: duckdb_connection
 number film release_date run_time film_rating n_won
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; &lt;dbl&gt;
 1 4 Monsters, Inc. 2001-11-02 92 G 1
 2 5 Finding Nemo 2003-05-30 100 G 1
 3 6 The Incredibles 2004-11-05 115 PG 2
 4 8 Ratatouille 2007-06-29 111 G 1
 5 9 WALL-E 2008-06-27 98 G 1
 6 10 Up 2009-05-29 96 PG 2
 7 11 Toy Story 3 2010-06-18 103 G 2
 8 13 Brave 2012-06-22 93 PG 1
 9 15 Inside Out 2015-06-19 95 PG 1
10 19 Coco 2017-11-22 105 PG 2
# … with more rows
</CODE></PRE>
]

---

# Join with postprocessing

.pull-left[
## Raw result

```r
pixar_films %>%
  left_join(academy_won, by = "film") %>%
  arrange(release_date)
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 6]
# Database: duckdb_connection
# Ordered by: release_date
 number film release_date run_time film_rating n_won
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; &lt;dbl&gt;
 1 1 Toy Story 1995-11-22 81 G NA
 2 2 A Bug&#039;s Life 1998-11-25 95 G NA
 3 3 Toy Story 2 1999-11-24 92 G NA
 4 4 Monsters, Inc. 2001-11-02 92 G 1
 5 5 Finding Nemo 2003-05-30 100 G 1
 6 6 The Incredibles 2004-11-05 115 PG 2
 7 7 Cars 2006-06-09 117 G NA
 8 8 Ratatouille 2007-06-29 111 G 1
 9 9 WALL-E 2008-06-27 98 G 1
10 10 Up 2009-05-29 96 PG 2
# … with more rows
</CODE></PRE>
]

.pull-right[
## After postprocessing

```r
pixar_films %>%
  left_join(academy_won, by = "film") %>%
  mutate(n_won = coalesce(n_won, 0L)) %>%
  arrange(release_date)
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 6]
# Database: duckdb_connection
# Ordered by: release_date
 number film release_date run_time film_rating n_won
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; &lt;dbl&gt;
 1 1 Toy Story 1995-11-22 81 G 0
 2 2 A Bug&#039;s Life 1998-11-25 95 G 0
 3 3 Toy Story 2 1999-11-24 92 G 0
 4 4 Monsters, Inc. 2001-11-02 92 G 1
 5 5 Finding Nemo 2003-05-30 100 G 1
 6 6 The Incredibles 2004-11-05 115 PG 2
 7 7 Cars 2006-06-09 117 G 0
 8 8 Ratatouille 2007-06-29 111 G 1
 9 9 WALL-E 2008-06-27 98 G 1
10 10 Up 2009-05-29 96 PG 2
# … with more rows
</CODE></PRE>
]

---

# Join with processing

Computed on the database, original data unchanged.

```r
pixar_films %>%
  left_join(academy_won, by = "film") %>%
  mutate(n_won = coalesce(n_won, 0L)) %>%
  arrange(release_date) %>%
  show_query()
```

```
<SQL>
SELECT "number", "film", "release_date", "run_time", "film_rating", COALESCE("n_won", 0) AS "n_won"
FROM (SELECT "number", "LHS"."film" AS "film", "release_date", "run_time", "film_rating", "n_won"
FROM "pixar_films" AS "LHS"
LEFT JOIN (SELECT "film", COUNT(*) AS "n_won"
FROM "academy"
WHERE ("status" = 'Won')
GROUP BY "film") "RHS"
ON ("LHS"."film" = "RHS"."film")
) "q01"
ORDER BY "release_date"
```

---

# Tables must be on the same source

Use `copy = TRUE` to enforce, the result is a lazy table if the LHS is a lazy table.

.pull-left[
## Bad

```r
try(
  academy %>%
    left_join(pixar_films_sqlite, by = "film")
)
```

<PRE class="fansi fansi-output"><CODE>Error in auto_copy(x, y, copy = copy, indexes = if (auto_index) list(by$y)) : 
 `x` and `y` must share the same src.
ℹ set `copy` = TRUE (may be slow).
</CODE></PRE>
]

.pull-right[
## Not too bad

```r
academy %>%
  left_join(pixar_films_sqlite, by = "film", copy = TRUE)
```

---

# Copying is expensive!

A temporary table is created on the LHS database.
If the RHS comes from a different database, results are temporarily loaded into the local session!

```r
academy %>%
  left_join(pixar_films_sqlite, by = "film", copy = TRUE) %>%
  show_query()
```

```
<SQL>
SELECT "LHS"."film" AS "film", "award_type", "status", "number", "release_date", "run_time", "film_rating"
FROM "academy" AS "LHS"
LEFT JOIN "dbplyr_002" AS "RHS"
ON ("LHS"."film" = "RHS"."film")
```

---

# Joining data frames with lazy tables

The result is a data frame too.

.pull-left[
## Bad

```r
try(
  pixarfilms::academy %>%
    left_join(pixar_films, by = "film")
)
```

<PRE class="fansi fansi-output"><CODE>Error in auto_copy(x, y, copy = copy) : 
 `x` and `y` must share the same src.
ℹ set `copy` = TRUE (may be slow).
</CODE></PRE>
]

.pull-right[
## Could be worse

```r
pixarfilms::academy %>%
  left_join(pixar_films, by = "film", copy = TRUE)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 80 × 7
 film award_type status number release_d… run_t… film_…
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 Toy Story Animated Fea… Award… 1 1995-11-22 81 G 
 2 Toy Story Original Scr… Nomin… 1 1995-11-22 81 G 
 3 Toy Story Adapted Scre… Ineli… 1 1995-11-22 81 G 
 4 Toy Story Original Sco… Nomin… 1 1995-11-22 81 G 
 5 Toy Story Original Song Nomin… 1 1995-11-22 81 G 
 6 Toy Story Other Won S… 1 1995-11-22 81 G 
 7 A Bug&#039;s Life Animated Fea… Award… 2 1998-11-25 95 G 
 8 A Bug&#039;s Life Adapted Scre… Ineli… 2 1998-11-25 95 G 
 9 A Bug&#039;s Life Original Sco… Nomin… 2 1998-11-25 95 G 
10 Toy Story 2 Animated Fea… Award… 3 1999-11-24 92 G 
# … with 70 more rows
</CODE></PRE>
]

---

# DuckDB: register data frames as database tables

Temporarily use a local data frame as a table.
Also works for Arrow datasets via `duckdb::duckdb_register_arrow()`.

.pull-left[
## Register and access

```r
duckdb::duckdb_register(
  con_duckdb,
  "academy_small",
  pixarfilms::academy[1:3, ]
)

academy_small <- tbl(con_duckdb, "academy_small")
academy_small
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;academy_small&gt; [?? x 3]
# Database: duckdb_connection
 film award_type status 
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; 
1 Toy Story Animated Feature Award not yet introduced
2 Toy Story Original Screenplay Nominated 
3 Toy Story Adapted Screenplay Ineligible 
</CODE></PRE>
]

.pull-right[
## Use

```r
academy_small %>%
  left_join(pixar_films, by = "film")
```

---

# DuckDB: Performance comparison

Baseline: Data frames.

.pull-left[

```r
nrow(nycflights13::flights)
```

```
[1] 336776
```
]

.pull-right[

```r
system.time(
  nycflights13::flights %>%
    count(year, month, day)
)
```

```
   user  system elapsed 
   0.01    0.00    0.01 
```
]

---

# DuckDB: Performance comparison

With registration.

.pull-left[

```r
system.time(duckdb::duckdb_register(
  con_duckdb,
  "flights",
  nycflights13::flights
))
```

```
   user  system elapsed 
  0.003   0.001   0.003 
```

```r
flights_register <- tbl(con_duckdb, "flights")
flights_register %>%
 count()
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 1]
# Database: duckdb_connection
 n
 &lt;dbl&gt;
1 336776
</CODE></PRE>
]

.pull-right[

```r
system.time(
  flights_register %>%
    count(year, month, day) %>%
    collect()
)
```

```
   user  system elapsed 
  0.031   0.000   0.030 
```
]

---

# DuckDB: Performance comparison

With copy.

.pull-left[

```r
system.time(
 flights_copy <- copy_to(con_duckdb, nycflights13::flights)
)
```

```
   user  system elapsed 
  0.087   0.008   0.095 
```

```r
flights_copy %>%
  count()
```

.pull-right[

```r
system.time(
  flights_copy %>%
    count(year, month, day) %>%
    collect()
)
```

```
   user  system elapsed 
  0.008   0.000   0.006 
```
]

---

# ETL, revisited

Insert a second table into our database.

```r
db_path <- fs::path_abs("pixar.duckdb")
con <- DBI::dbConnect(duckdb::duckdb(dbdir = db_path))
DBI::dbWriteTable(con, "academy", pixarfilms::academy, overwrite = TRUE)
DBI::dbExecute(con, "CREATE UNIQUE INDEX academy_pk ON academy (film, award_type)")
```

```
[1] 0
```

```r
DBI::dbExecute(con, "CREATE INDEX academy_fk ON academy (film)")
```

```
[1] 0
```

```r
DBI::dbDisconnect(con)
```

---

background-image: url("data:image/png;base64,#images/21-frame.webp")
background-size: 40%
background-position: 100% 100%

# Joins: Exercises 1

.pull-left[
1. How many rows does the join between `academy` and `pixar_films` contain?
   Try to find out without loading all the data into memory. Explain.
2. Which films are not yet listed in the `academy` table? What does the
   resulting SQL query look like?
   - Hint: Use `anti_join()`
]

---

background-image: url("data:image/png;base64,#images/21-frame.webp")
background-size: 40%
background-position: 100% 100%

# Joins: Exercises 2

.pull-left[
3. Transform `academy` into a wide table so that there is at most one row
   per film. Join the resulting table with the `pixar_films` table.
   - Hint: Use `pivot_wider()`, `spread()`, `dcast()`, ... . You need to
     compute locally, because these functions don't work on the database.
]

.pull-right[
4. Plot a bar chart with the number of awards won and nominated per year.
   Compute as much as possible on the database.
   - Hint: "Long form" or "wide form"?
]

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- **The {dm} package**
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/22.webp")
background-size: 40%
background-position: 100% 100%

# Data models

.pull-left[
- Compound object for multiple tables
- Features

Script: `databases_22.R`

```r
library(tidyverse)
```
]

---

background-image: url("data:image/png;base64,#images/22-frame.webp")
background-size: 40%
background-position: 100% 100%

# Data models

.pull-left[
- Compound object for multiple tables
- Features

Script: `databases_22.R`

```r
library(tidyverse)
```
]

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- **A bit of theory**
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/23.webp")
background-size: 40%
background-position: 100% 100%

# Data models

.pull-left[
- Keys, relationships, constraints
- Zooming

Script: `databases_23.R`

```r
library(tidyverse)
```
]

---

background-image: url("data:image/png;base64,#images/23-frame.webp")
background-size: 40%
background-position: 100% 100%

# Data models

.pull-left[
- Keys, relationships, constraints
- Zooming

Script: `databases_23.R`

```r
library(tidyverse)
```
]

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- A bit of theory
- **Playing the whole game**
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/24.webp")
background-size: 40%
background-position: 100% 100%

# The whole game

.pull-left[
- Build a local data model
- Copy it to the database
- Consume it

Script: `databases_24.R`

```r
library(tidyverse)
```
]

---

background-image: url("data:image/png;base64,#images/24-frame.webp")
background-size: 40%
background-position: 100% 100%

# The whole game

.pull-left[
- Build a local data model
- Copy it to the database
- Consume it

Script: `databases_24.R`

```r
library(tidyverse)
```
]

---

# Recap

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions